unit CSVParser;

interface

uses
   Classes
  ,Contnrs
  ,Db
  ,DbClient
  ,TypeTest
  ;

type

  ICSVParser = interface
  ['{26DE43DA-8651-4CC0-8BCD-9201E7D64CE6}']
    function GetDataSet(Index: integer): TDataSet;
    function GetDataSetName(Index: integer): string;
    function DataSetCount: integer;
    property DataSetName[ Index: integer ]: string read GetDataSetName;
    property DataSet[ Index: integer ]: TDataSet read GetDataSet;
    end;

  TCSVParser = class( TInterfacedObject, ICSVParser )
  private
    FFileName: string;
    FRawList: TStringList;
    FTableList: TStringList;
    FTypeTest: ITypeTest;
    FDataSets: TObjectList;
    procedure Load;
    procedure SplitTables;
    procedure PopulateDataSets;
    procedure PopulateDataSet( const DataSet: TClientDataSet;
      TableName: string;
      const TableContents: TStringList );
    function GetDataSet(Index: integer): TDataSet;
    function GetDataSetName(Index: integer): string;
  public
    constructor Create( CSVFileName: string );
    destructor Destroy; override;
    function DataSetCount: integer;
    property DataSetName[ Index: integer ]: string read GetDataSetName;
    property DataSet[ Index: integer ]: TDataSet read GetDataSet;
    end;

implementation

uses
   SysUtils
  ;

{ TCSVParser }

constructor TCSVParser.Create( CSVFileName: string );
  begin
  inherited Create;
  FRawList  := TStringList.Create;
  FTableList := TStringList.Create;
  FTypeTest := TTypeTest.Create as ITypeTest;
  FDataSets := TObjectList.Create;
  FFileName := CSVFileName;
  Load;
  end;

function TCSVParser.DataSetCount: integer;
  begin
  Result := FDataSets.Count;
  end;

destructor TCSVParser.Destroy;
  var
  table_counter: integer;
  row_counter: integer;
  a_table: TStringList;
  begin
  FRawList.Free;
  for table_counter := FTableList.Count - 1 downto 0 do
    begin
    a_table := TStringList( FTableList.Objects[ table_counter ] );
    for row_counter := a_table.Count -1 downto 0 do
      a_table.Objects[ row_counter ].Free;
    a_table.Free;
    end;
  FTableList.Free;
  FDataSets.Free;
  inherited;
  end;

function TCSVParser.GetDataSet(Index: integer): TDataSet;
  begin
  Result := TDataSet( FDataSets[ Index ] );
  end;

function TCSVParser.GetDataSetName(Index: integer): string;
  begin
  Result := GetDataSet( Index ).Name;
  end;

procedure TCSVParser.Load;
  begin
  FRawList.LoadFromFile( FFileName );
  SplitTables;
  PopulateDataSets;
  end;


procedure TCSVParser.PopulateDataSet(const DataSet: TClientDataSet;
  TableName: string; const TableContents: TStringList);
  var
  field_count: integer;
  definition_row: TStrings;

  procedure establish_data_set( const DefinitionLine: TStrings );
    var
    counter: integer;
    field_type: TFieldType;
    field_length: integer;
    begin
    field_count := DefinitionLine.Count;
    for counter := 3 to field_count - 1 do
      begin
      field_type := TFieldType( DefinitionLine.Objects[ counter ] );
      if field_type = ftString then
        field_length := 50
      else
        field_length := 0;
      DataSet.FieldDefs.Add( DefinitionLine.Strings[ counter ], field_type, field_length );
      end;
    end;

  procedure add_row( const DataRow: TStrings );
    var
    counter: integer;
    field_type: TFieldType;
    i: integer;
    f: extended;
    d: TDateTime;
    begin
    DataSet.Append;
    for counter := 3 to field_count - 1 do
      begin
      field_type := TFieldType( definition_row.Objects[ counter ] );
      case field_type of
        ftString:
          DataSet.FieldByName( definition_row.Strings[ counter ] ).AsString := DataRow.Strings[ counter ];
        ftInteger:
          begin
          if FTypeTest.IntegerStrToInt( DataRow.Strings[ counter ], i ) then
            DataSet.FieldByName( definition_row.Strings[ counter ] ).AsInteger := i;
          end;
        ftFloat:
          begin
          if FTypeTest.FloatStrToFloat( DataRow.Strings[ counter ], f ) then
            DataSet.FieldByName( definition_row.Strings[ counter ] ).AsFloat := f;
          end;
        ftDateTime:
          begin
          if FTypeTest.DateTimeStrToDateTime( DataRow.Strings[ counter ], d ) then
            DataSet.FieldByName( definition_row.Strings[ counter ] ).AsDateTime := d;
          end;
      else
        raise Exception.Create( 'TCSVParser.PopulateDataSet: field type mismatch' );
        end;
      end;
    end;

  var
  counter: integer;
  len: integer;
  begin
  len := TableContents.Count - 1;
  definition_row := TStringList( TableContents.Objects[ 0 ] );
  establish_data_set( definition_row );
  DataSet.CreateDataSet;
  //Add data
  for counter := 1 to len do
    add_row( TStringList( TableContents.Objects[ counter ] ) );
  end;

procedure TCSVParser.PopulateDataSets;
  var
  table_counter: integer;
  data_set: TClientDataSet;
  begin
  for table_counter := 0 to FTableList.Count - 1 do
    begin
    data_set := TClientDataSet.Create( nil );
    data_set.Name := FTableList.Strings[ table_counter ];
    FDataSets.Add( data_set );
    PopulateDataSet( data_set, FTableList.Strings[ table_counter ], TStringList( FTableList.Objects[ table_counter ] ) );
    end;
  end;

{
  FTableList contains a list of table names and associated stringlists.
  This routine iterates over each line in the FRawList (the data from the csv file) and..
  - each time the table name (field) changes it creates a stringlist ("tabledata") and adds it to FTableList,
    with its name, and with a stringlist populated with its field names as its first object.
  - as each data row within a table is encountered another stringlist is created,
    populated with the data and added to tabledata.

}
procedure TCSVParser.SplitTables;
  var
  raw_list_counter: integer;
  line_count: integer;
  new_table: TStringList;

  procedure establish_field_types( const DataList: TStringList );
    var
    counter: integer;
    field_list: TStringList;
    begin
    field_list := TStringList( new_table.Objects[ 0 ] );
    for counter := 3 to field_list.Count - 1 do
      field_list.Objects[ counter ] := TObject( ord( FTypeTest.FieldTypeTest( DataList.Strings[ counter ] ) ) );
    end;   
  {
     Creates a StringList to hold the data for a new table.
     Creates another stringlist, populates it with field names and adds it to the new list.
     Adds the new list to FTableList.
     Returns the new list
  }
  function create_new_table( TableName: string ): TStringList;
    var
    field_list: TStringList;
    begin
    Result := TStringList.Create;
    FTableList.AddObject( TableName, Result );
    field_list := TStringList.Create;
    field_list.CommaText := FRawList.Strings[ raw_list_counter ];
    Result.AddObject( 'FieldNameList', field_list );
    end;

  var
  table_name: string;
  new_line: TStringList;

  data_row: TStringList;
  is_typed: boolean;
  begin
  table_name := '';
  is_typed := False;
  line_count := FRawlist.Count;
  new_line := TStringList.Create;
  try
    //iterate over the lines of the csv file..
    for raw_list_counter := 1 to line_count - 1 do
      begin
      //load the comma separated values in each line into the 'new-line' stringlist..
      new_line.CommaText := FRawList.Strings[ raw_list_counter ];
      if new_line.Strings[ 1 ] = 'END OF REPORT' then
        Break; //==========>
      // if there's a change of table..
      if new_line.Strings[ 2 ] <> table_name then
        begin
        table_name := new_line.Strings[ 2 ];
        is_typed := False;
        //Add a stringlist to FTableList with the first object
        //being a stringlist populated with fieldnames.
        new_table := create_new_table( table_name )
        end
      else
        begin
        data_row := TStringList.Create;
        data_row.CommaText := FRawList.Strings[ raw_list_counter ];
        if not is_typed then
          begin
          establish_field_types( data_row );
          is_typed := True;
          end;
        new_table.AddObject( 'table_name', data_row );
        end;
      end;
  finally
    new_line.Free;
    end;
  end;


end.
